<?php
/////////////////////////////////////////////////
// This function use for migrate raw data to temp data
/////////////////////////////////////////////////

function VoteDataCenter_suratreport_report(){
  $form = FormUtil::getPassedValue ('form', null, 'REQUEST');
  //print_r($form); exit;

  $render = pnRender::getInstance('VoteDataCenter');
  
  if(!isset($form['khet_id']) || $form['khet_id'] == 0){


    /////////////////////////////////////////
    //
    // Count all province
    //
    /////////////////////////////////////////
    //Count all person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_person
            FROM 
              suratdemap.pn_degis_contact 
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_person"
              );

    $result = DBUtil::executeSQL($query);
    $allPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_unknown_person
            FROM 
              suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_unknown_person"
              );

    $result = DBUtil::executeSQL($query);
    $allUnknownPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person mark
    $query = "SELECT 
                aco_province_id,
                COUNT(aco_id) as count_all_remark_person
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_remark_person"
              );

    $result = DBUtil::executeSQL($query);
    $allRemarkPersonArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count
    $render->assign('count_all_person', $allPersonArray[0]['count_all_person']);
    $render->assign('count_all_unknown_person', $allUnknownPersonArray[0]['count_all_unknown_person']);
    $render->assign('count_all_remark_person', $allRemarkPersonArray[0]['count_all_remark_person']);


    /////////////////////////////////////////
    //
    // Count value from khet
    //
    /////////////////////////////////////////
    //Count all khet mark
    $query = "SELECT 
                aco_khet_id,
                COUNT(aco_id) AS count_all_khet
              FROM 
                suratdemap.pn_degis_contact 
              GROUP BY aco_khet_id
              ORDER BY aco_khet_id ASC
            ";
    $column = array(
              "name", "count_all_khet"
              );

    $result = DBUtil::executeSQL($query);
    $allKhetArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_khet_id,
                COUNT(aco_id) AS count_unkown_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              GROUP BY aco_khet_id
              ORDER BY aco_khet_id ASC
            ";
    $column = array(
              "name", "count_unkown_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetUnknownArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_khet_id,
                COUNT(aco_id) AS count_remark_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              GROUP BY aco_khet_id
              ORDER BY aco_khet_id ASC
            ";
    $column = array(
              "name", "count_remark_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetRemarkArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count khet

    $sum_count_all         = 0;
    $sum_count_we          = 0;
    $sum_count_unknown     = 0;
    $sum_all_count_unknown = 0;

    for($i=0; $i<count($allKhetArray); $i++){
        $objectArray[$i]['name']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['khet_id']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['count_all_khet']    = $allKhetArray[$i]['count_all_khet'];
        $objectArray[$i]['count_unkown_khet'] = $khetUnknownArray[$i]['count_unkown_khet'];
        $objectArray[$i]['count_remark_khet'] = $khetRemarkArray[$i]['count_remark_khet'];


        $sum_count_all += $allKhetArray[$i]['count_all_khet'];
        $sum_count_we += $khetRemarkArray[$i]['count_remark_khet'];
        $sum_all_count_unknown += $khetUnknownArray[$i]['count_unkown_khet'];

    }


    $render->assign('sum_count_all', $sum_count_all);
    $render->assign('sum_count_we', $sum_count_we);
    $render->assign('sum_all_count_unknown', $sum_all_count_unknown);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratreport.htm');

  }else if($form['khet_id'] == 1){

    /////////////////////////////////////////
    //
    // Count value from khet
    //
    /////////////////////////////////////////
    //Count all person
    $query = "
            SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_person
            FROM 
              suratdemap.pn_degis_contact 
            WHERE
              aco_khet_id = 1
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_person"
              );

    $result = DBUtil::executeSQL($query);
    $allPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_unknown_person
            FROM 
              suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 1
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_unknown_person"
              );

    $result = DBUtil::executeSQL($query);
    $allUnknownPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person mark
    $query = "SELECT 
                aco_province_id,
                COUNT(aco_id) as count_all_remark_person
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 1
              GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_remark_person"
              );

    $result = DBUtil::executeSQL($query);
    $allRemarkPersonArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count
    $render->assign('count_all_person', $allPersonArray[0]['count_all_person']);
    $render->assign('count_all_unknown_person', $allUnknownPersonArray[0]['count_all_unknown_person']);
    $render->assign('count_all_remark_person', $allRemarkPersonArray[0]['count_all_remark_person']);


    /////////////////////////////////////////
    //
    // Count value from unit
    //
    /////////////////////////////////////////

    //Count all khet mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_all_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE
                aco_khet_id = 1
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_khet"
              );

    $result = DBUtil::executeSQL($query);
    $allKhetArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_unkown_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 1
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_unkown_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetUnknownArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_remark_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 1
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_remark_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetRemarkArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count khet

    $sum_count_all         = 0;
    $sum_count_we          = 0;
    $sum_count_unknown     = 0;
    $sum_all_count_unknown = 0;

    for($i=0; $i<count($allKhetArray); $i++){
        $objectArray[$i]['name']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['khet_id']              = 1;
        $objectArray[$i]['unit']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['count_all_khet']    = $allKhetArray[$i]['count_all_khet'];
        $objectArray[$i]['count_unkown_khet'] = $khetUnknownArray[$i]['count_unkown_khet'];
        $objectArray[$i]['count_remark_khet'] = $khetRemarkArray[$i]['count_remark_khet'];


        $sum_count_all += $allKhetArray[$i]['count_all_khet'];
        $sum_count_we += $khetRemarkArray[$i]['count_remark_khet'];
        $sum_all_count_unknown += $khetUnknownArray[$i]['count_unkown_khet'];

    }


    $render->assign('sum_count_all', $sum_count_all);
    $render->assign('sum_count_we', $sum_count_we);
    $render->assign('sum_all_count_unknown', $sum_all_count_unknown);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratreport.htm');

  }else if($form['khet_id'] == 2){

    /////////////////////////////////////////
    //
    // Count all province
    //
    /////////////////////////////////////////
    //Count all person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_person
            FROM 
              suratdemap.pn_degis_contact 
            WHERE
              aco_khet_id = 2
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_person"
              );

    $result = DBUtil::executeSQL($query);
    $allPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_unknown_person
            FROM 
              suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 2
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_unknown_person"
              );

    $result = DBUtil::executeSQL($query);
    $allUnknownPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person mark
    $query = "SELECT 
                aco_province_id,
                COUNT(aco_id) as count_all_remark_person
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 2
              GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_remark_person"
              );

    $result = DBUtil::executeSQL($query);
    $allRemarkPersonArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count
    $render->assign('count_all_person', $allPersonArray[0]['count_all_person']);
    $render->assign('count_all_unknown_person', $allUnknownPersonArray[0]['count_all_unknown_person']);
    $render->assign('count_all_remark_person', $allRemarkPersonArray[0]['count_all_remark_person']);

    /////////////////////////////////////////
    //
    // Count value from aco_unit
    //
    /////////////////////////////////////////

    //Count all khet mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_all_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE
                aco_khet_id = 2
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_khet"
              );

    $result = DBUtil::executeSQL($query);
    $allKhetArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_unkown_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 2
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_unkown_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetUnknownArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_remark_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 2
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_remark_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetRemarkArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count khet

    $sum_count_all         = 0;
    $sum_count_we          = 0;
    $sum_count_unknown     = 0;
    $sum_all_count_unknown = 0;

    for($i=0; $i<count($allKhetArray); $i++){
        $objectArray[$i]['name']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['khet_id']              = 2;
        $objectArray[$i]['unit']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['count_all_khet']    = $allKhetArray[$i]['count_all_khet'];
        $objectArray[$i]['count_unkown_khet'] = $khetUnknownArray[$i]['count_unkown_khet'];
        $objectArray[$i]['count_remark_khet'] = $khetRemarkArray[$i]['count_remark_khet'];


        $sum_count_all += $allKhetArray[$i]['count_all_khet'];
        $sum_count_we += $khetRemarkArray[$i]['count_remark_khet'];
        $sum_all_count_unknown += $khetUnknownArray[$i]['count_unkown_khet'];

    }


    $render->assign('sum_count_all', $sum_count_all);
    $render->assign('sum_count_we', $sum_count_we);
    $render->assign('sum_all_count_unknown', $sum_all_count_unknown);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratreport.htm');

  }else if($form['khet_id'] == 3){

    /////////////////////////////////////////
    //
    // Count all province
    //
    /////////////////////////////////////////
    //Count all person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_person
            FROM 
              suratdemap.pn_degis_contact 
            WHERE
              aco_khet_id = 3
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_person"
              );

    $result = DBUtil::executeSQL($query);
    $allPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person
    $query = "SELECT 
              aco_province_id,
              COUNT(aco_id) as count_all_unknown_person
            FROM 
              suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 3
            GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_unknown_person"
              );

    $result = DBUtil::executeSQL($query);
    $allUnknownPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person mark
    $query = "SELECT 
                aco_province_id,
                COUNT(aco_id) as count_all_remark_person
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 3
              GROUP BY aco_province_id
            ";
    $column = array(
              "name", "count_all_remark_person"
              );

    $result = DBUtil::executeSQL($query);
    $allRemarkPersonArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count
    $render->assign('count_all_person', $allPersonArray[0]['count_all_person']);
    $render->assign('count_all_unknown_person', $allUnknownPersonArray[0]['count_all_unknown_person']);
    $render->assign('count_all_remark_person', $allRemarkPersonArray[0]['count_all_remark_person']);


    /////////////////////////////////////////
    //
    // Count value from khet
    //
    /////////////////////////////////////////

    //Count all khet mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_all_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE
                aco_khet_id = 3
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_khet"
              );

    $result = DBUtil::executeSQL($query);
    $allKhetArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_unkown_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 3
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_unkown_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetUnknownArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_remark_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 3
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_remark_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetRemarkArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count khet

    $sum_count_all         = 0;
    $sum_count_we          = 0;
    $sum_count_unknown     = 0;
    $sum_all_count_unknown = 0;

    for($i=0; $i<count($allKhetArray); $i++){
        $objectArray[$i]['name']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['khet_id']              = 3;
        $objectArray[$i]['unit']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['count_all_khet']    = $allKhetArray[$i]['count_all_khet'];
        $objectArray[$i]['count_unkown_khet'] = $khetUnknownArray[$i]['count_unkown_khet'];
        $objectArray[$i]['count_remark_khet'] = $khetRemarkArray[$i]['count_remark_khet'];


        $sum_count_all += $allKhetArray[$i]['count_all_khet'];
        $sum_count_we += $khetRemarkArray[$i]['count_remark_khet'];
        $sum_all_count_unknown += $khetUnknownArray[$i]['count_unkown_khet'];

    }


    $render->assign('sum_count_all', $sum_count_all);
    $render->assign('sum_count_we', $sum_count_we);
    $render->assign('sum_all_count_unknown', $sum_all_count_unknown);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratreport.htm');

  }else if($form['khet_id'] == 4){

    /////////////////////////////////////////
    //
    // Count all province
    //
    /////////////////////////////////////////
    //Count all person
    $query = "SELECT 
              aco_unit,
              COUNT(aco_id) as count_all_person
            FROM 
              suratdemap.pn_degis_contact 
            WHERE
              aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_person"
              );

    $result = DBUtil::executeSQL($query);
    $allPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person
    $query = "SELECT 
              aco_unit,
              COUNT(aco_id) as count_all_unknown_person
            FROM 
              suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_unknown_person"
              );

    $result = DBUtil::executeSQL($query);
    $allUnknownPersonArray = DBUtil::marshallObjects ($result, $column);

    //Count all unknown person mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) as count_all_remark_person
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_remark_person"
              );

    $result = DBUtil::executeSQL($query);
    $allRemarkPersonArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count
    $render->assign('count_all_person', $allPersonArray[0]['count_all_person']);
    $render->assign('count_all_unknown_person', $allUnknownPersonArray[0]['count_all_unknown_person']);
    $render->assign('count_all_remark_person', $allRemarkPersonArray[0]['count_all_remark_person']);


    /////////////////////////////////////////
    //
    // Count value from khet
    //
    /////////////////////////////////////////

    //Count all khet mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_all_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE
                aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_all_khet"
              );

    $result = DBUtil::executeSQL($query);
    $allKhetArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_unkown_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 0 
              AND
                aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_unkown_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetUnknownArray = DBUtil::marshallObjects ($result, $column);


    //Count all khet unkown mark
    $query = "SELECT 
                aco_unit,
                COUNT(aco_id) AS count_remark_khet
              FROM 
                suratdemap.pn_degis_contact 
              WHERE 
                aco_remarkv = 1 
              AND
                aco_khet_id = 4
              GROUP BY aco_unit
              ORDER BY aco_unit ASC
            ";
    $column = array(
              "name", "count_remark_khet"
              );

    $result = DBUtil::executeSQL($query);
    $khetRemarkArray = DBUtil::marshallObjects ($result, $column);

    //Assign value from count khet

    $sum_count_all         = 0;
    $sum_count_we          = 0;
    $sum_count_unknown     = 0;
    $sum_all_count_unknown = 0;

    for($i=0; $i<count($allKhetArray); $i++){
        $objectArray[$i]['name']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['khet_id']           = 4;
        $objectArray[$i]['unit']              = $allKhetArray[$i]['name'];
        $objectArray[$i]['count_all_khet']    = $allKhetArray[$i]['count_all_khet'];
        $objectArray[$i]['count_unkown_khet'] = $khetUnknownArray[$i]['count_unkown_khet'];
        $objectArray[$i]['count_remark_khet'] = $khetRemarkArray[$i]['count_remark_khet'];


        $sum_count_all += $allKhetArray[$i]['count_all_khet'];
        $sum_count_we += $khetRemarkArray[$i]['count_remark_khet'];
        $sum_all_count_unknown += $khetUnknownArray[$i]['count_unkown_khet'];

    }


    $render->assign('sum_count_all', $sum_count_all);
    $render->assign('sum_count_we', $sum_count_we);
    $render->assign('sum_all_count_unknown', $sum_all_count_unknown);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratreport.htm');

  }

}

function VoteDataCenter_suratreport_person(){
  $khet_id = FormUtil::getPassedValue ('khet_id', null, 'REQUEST');
  $unit    = FormUtil::getPassedValue ('unit', null, 'REQUEST');
  $select  = FormUtil::getPassedValue ('select', null, 'REQUEST');

  if($khet_id){
    $where[] = "aco_khet_id = ". $khet_id .""; 
  }
  if($unit){
    $where[] = "aco_unit = ". $unit .""; 
  }

  $wheres = implode(' AND ', $where) ;

  if($select == "we"){
    $title = "รายชื่อคนที่เลือกเรา";
    $query = "
              SELECT 	`aco_id`,
                `aco_province_id`, 
                `aco_district_id`, 
                `aco_tumbol_id`, 
                `aco_person_id`, 
                `aco_prefix_name`, 
                `aco_first_name`, 
                `aco_addr_house_no`, 
                IF(INSTR(aco_addr_house_no, '/')=0,0,SUBSTRING_INDEX( aco_addr_house_no , '/', -1 )) AS aco_sub_addr_house_no,
                `aco_addr_moo`, 
                `aco_addr_trok`, 
                `aco_addr_soi`, 
                `aco_addr_road`
              FROM 
                `suratdemap`.`pn_degis_contact` 
              WHERE 
              ".$wheres."
              AND 
                aco_remarkv = 1    
              ORDER BY CAST(aco_addr_house_no AS SIGNED INTEGER), aco_sub_addr_house_no, aco_id,aco_last_name 
            ";
  }else if($select == "unknown"){

    $title = "รายชื่อคนที่ยังไม่ตัดสินใจเลือก";
    $query = "
              SELECT 	`aco_id`,
                `aco_province_id`, 
                `aco_district_id`, 
                `aco_tumbol_id`, 
                `aco_person_id`, 
                `aco_prefix_name`, 
                `aco_first_name`, 
                `aco_addr_house_no`, 
                IF(INSTR(aco_addr_house_no, '/')=0,0,SUBSTRING_INDEX( aco_addr_house_no , '/', -1 )) AS aco_sub_addr_house_no,
                `aco_addr_moo`, 
                `aco_addr_trok`, 
                `aco_addr_soi`, 
                `aco_addr_road`
              FROM 
                `suratdemap`.`pn_degis_contact` 
              WHERE 
              ".$wheres."
              AND 
                aco_remarkv = 0    
              ORDER BY CAST(aco_addr_house_no AS SIGNED INTEGER), aco_sub_addr_house_no, aco_last_name ";
  }

  $column = array(
                "aco_id",
                "aco_province_id", 
                "aco_district_id", 
                "aco_tumbol_id", 
                "aco_person_id", 
                "aco_prefix_name", 
                "aco_first_name", 
                "aco_addr_house_no", 
                "aco_sub_addr_house_no",
                "aco_addr_moo", 
                "aco_addr_trok", 
                "aco_addr_soi", 
                "aco_addr_road"
            );

  $result = DBUtil::executeSQL($query);
  $objectArray = DBUtil::marshallObjects ($result, $column);

  $render = pnRender::getInstance('VoteDataCenter');
  $render->assign('objectArray', $objectArray);
  $render->assign('title', $title);
  return $render->fetch('admin_remark_suratperson.htm');

}



function VoteDataCenter_suratreport_mappoll(){
  $form = FormUtil::getPassedValue ('form', null, 'REQUEST');
  //print_r($form); exit;

  $render = pnRender::getInstance('VoteDataCenter');
  
  if(trim(isset($form['house_no']))){

    //Count all khet unkown mark
    $query = "SELECT 
                id,
                house_no,
                khet,
                unit
              FROM 
                suratsurvay.survayvote 
              WHERE 
                house_no = ".$form['house_no']."
              ORDER BY khet ASC
            ";
    $column = array(
                  'id',
                  'house_no',
                  'khet',
                  'unit'
              );

    $result = DBUtil::executeSQL($query);
    $objectArray = DBUtil::marshallObjects ($result, $column);

    $render->assign('objectArray', $objectArray);

    return $render->fetch('admin_remark_suratmappoll.htm');
  }else{


    $render->assign('objectArray', false);
    return $render->fetch('admin_remark_suratmappoll.htm');
  }
}


function VoteDataCenter_suratreport_editmap(){

  $id = FormUtil::getPassedValue ('id', null, 'REQUEST');

  $render = pnRender::getInstance('VoteDataCenter');
    //Count all khet unkown mark
    $query = "SELECT
                id,
                house_no,
                khet,
                unit,
                vote_member_1,
                vote_member_1_no,
                vote_member_2,
                vote_member_2_no,
                vote_member_3,
                vote_member_3_no,
                vote_member_4,
                vote_member_4_no,
                vote_member_5,
                vote_member_5_no,
                vote_member_6,
                vote_member_6_no,
                vote,
                vote_no,
                latitude,
                longitude
              FROM 
                suratsurvay.survayvote 
              WHERE 
                id = ".$id."
              ORDER BY khet ASC
            ";
    $column = array(
                    'id',
                    'house_no',
                    'khet',
                    'unit',
                    'vote_member_1',
                    'vote_member_1_no',
                    'vote_member_2',
                    'vote_member_2_no',
                    'vote_member_3',
                    'vote_member_3_no',
                    'vote_member_4',
                    'vote_member_4_no',
                    'vote_member_5',
                    'vote_member_5_no',
                    'vote_member_6',
                    'vote_member_6_no',
                    'vote',
                    'vote_no',
                    'latitude',
                    'longitude'
              );

    $result = DBUtil::executeSQL($query);
    $objectArray = DBUtil::marshallObjects ($result, $column);

    $render->assign('form', $objectArray[0]);

    return $render->fetch('admin_edit_suratmappoll.htm');


}



function VoteDataCenter_suratreport_submit(){

  //$id = FormUtil::getPassedValue ('id', null, 'REQUEST');
  $form = FormUtil::getPassedValue ('form', null, 'REQUEST');

  $render = pnRender::getInstance('VoteDataCenter');

  $upadteQuery = "UPDATE suratsurvay.survayvote  SET latitude='".$form['latitude']."', longitude='".$form['longitude']."' WHERE id=".$form['id'];
  DBUtil::executeSQL($upadteQuery);

    //Count all khet unkown mark
    $query = "SELECT
                id,
                house_no,
                khet,
                unit,
                vote_member_1,
                vote_member_1_no,
                vote_member_2,
                vote_member_2_no,
                vote_member_3,
                vote_member_3_no,
                vote_member_4,
                vote_member_4_no,
                vote_member_5,
                vote_member_5_no,
                vote_member_6,
                vote_member_6_no,
                vote,
                vote_no,
                latitude,
                longitude
              FROM 
                suratsurvay.survayvote 
              WHERE 
                id = ".$form['id']."
              ORDER BY khet ASC
            ";
    $column = array(
                    'id',
                    'house_no',
                    'khet',
                    'unit',
                    'vote_member_1',
                    'vote_member_1_no',
                    'vote_member_2',
                    'vote_member_2_no',
                    'vote_member_3',
                    'vote_member_3_no',
                    'vote_member_4',
                    'vote_member_4_no',
                    'vote_member_5',
                    'vote_member_5_no',
                    'vote_member_6',
                    'vote_member_6_no',
                    'vote',
                    'vote_no',
                    'latitude',
                    'longitude'
              );

    $result = DBUtil::executeSQL($query);
    $objectArray = DBUtil::marshallObjects ($result, $column);

    $render->assign('form', $objectArray[0]);

    return $render->fetch('admin_edit_suratmappoll.htm');


}


function VoteDataCenter_suratreport_pollreport(){

  //$id = FormUtil::getPassedValue ('id', null, 'REQUEST');
  $form = FormUtil::getPassedValue ('form', null, 'REQUEST');



  if($form['khet_id']){
    $where[] = "khet = ". $form['khet_id'].""; 
  }
  if($form['vote_no']){
    $where[] = "vote_no = ". $form['vote_no'] .""; 
  }

  $wheres = implode(' AND ', $where) ;

  $render = pnRender::getInstance('VoteDataCenter');
  $render->assign('form', $objectArray);

    //Count all khet unkown mark
    $query = "SELECT
                id,
                house_no,
                khet,
                unit,
                vote_member_1,
                vote_member_1_no,
                vote_member_2,
                vote_member_2_no,
                vote_member_3,
                vote_member_3_no,
                vote_member_4,
                vote_member_4_no,
                vote_member_5,
                vote_member_5_no,
                vote_member_6,
                vote_member_6_no,
                vote,
                vote_no,
                latitude,
                longitude
              FROM 
                suratsurvay.survayvote 
              WHERE 
              ". $wheres ."
              ORDER BY khet ASC
            ";
    $column = array(
                    'id',
                    'house_no',
                    'khet',
                    'unit',
                    'vote_member_1',
                    'vote_member_1_no',
                    'vote_member_2',
                    'vote_member_2_no',
                    'vote_member_3',
                    'vote_member_3_no',
                    'vote_member_4',
                    'vote_member_4_no',
                    'vote_member_5',
                    'vote_member_5_no',
                    'vote_member_6',
                    'vote_member_6_no',
                    'vote',
                    'vote_no',
                    'latitude',
                    'longitude'
              );

    $result = DBUtil::executeSQL($query);
    $objectArray = DBUtil::marshallObjects ($result, $column);

    $render->assign('objectArray', $objectArray);


  return $render->fetch('admin_remark_suratpollreport.htm');


}


?>